package com.andy.blog.common.utils;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.andy.blog.common.constant.BaseConstants;
import com.andy.blog.common.constant.FileTypeConstant;
import com.google.common.base.Stopwatch;
import org.apache.commons.collections4.ListUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.util.List;

/**
 * Created by andy on 2023-04-29 11:41
 */
public class EasyExcelUtils {
    /**
     * 每个sheet页默认显示数据条数: 20W
     */
    private static final Integer PER_SHEET_ROW_COUNT = 200000;

    private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtils.class);

    private EasyExcelUtils() {
    }

    /**
     * 读取Excel（一个sheet）
     *
     * @param excel   文件
     * @param clazz   实体类
     * @param sheetNo sheet序号
     * @return 返回实体列表(需转换)
     */
    //public static <T> List<T> readExcel(MultipartFile excel, Class<T> clazz, int sheetNo) {
    //
    //    ExcelListener<T> excelListener = new ExcelListener<>(clazz);
    //
    //    ExcelReader excelReader = getReader(excel, clazz, excelListener);
    //    if (excelReader == null) {
    //        return Lists.newArrayList();
    //    }
    //
    //    ReadSheet readSheet = EasyExcelFactory.readSheet(sheetNo).build();
    //    excelReader.read(readSheet);
    //    excelReader.finish();
    //
    //    return TransformUtils.transformList(excelListener.getDataList(), clazz);
    //}

    /**
     * 读取Excel（多个sheet可以用同一个实体类解析）
     *
     * @param excel 文件
     * @param clazz 实体类
     * @return 返回实体列表(需转换)
     */
    //public static <T> List<T> readExcel(MultipartFile excel, Class<T> clazz) {
    //
    //    ExcelListener<T> excelListener = new ExcelListener<>(clazz);
    //
    //    ExcelReader excelReader = getReader(excel, clazz, excelListener);
    //
    //    if (excelReader == null) {
    //        return Lists.newArrayList();
    //    }
    //
    //    List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
    //
    //    for (ReadSheet readSheet : readSheetList) {
    //        excelReader.read(readSheet);
    //    }
    //    excelReader.finish();
    //
    //    return TransformUtils.transformList(excelListener.getDataList(), clazz);
    //}

    /**
     * 导出Excel(一个sheet)
     *
     * @param response  HttpServletResponse
     * @param dataList  数据list
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的sheet名
     * @param clazz     实体类
     */
    public static <T> void writeExcel(HttpServletResponse response, List<T> dataList, String fileName, String sheetName, Class<T> clazz) {

        OutputStream outputStream = getOutputStream(response, fileName);

        ExcelWriter excelWriter = EasyExcelFactory.write(outputStream, clazz).build();
        WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetName).build();

        excelWriter.write(dataList, writeSheet);

        excelWriter.finish();
    }

    /**
     * 导出多个sheet页, 同一个对象
     *
     * @param response  response
     * @param list      导出数据
     * @param fileName  文件名
     * @param sheetName sheet页
     * @param clazz     对象
     * @param size      sheet页展示的条数
     */
    public static <T> void writeSheetExcel(HttpServletResponse response, List<T> list, String fileName, String sheetName, int size, Class<T> clazz) {

        logger.info("<<--导出Excel开始-->>");
        Stopwatch stopwatch = Stopwatch.createStarted();

        OutputStream outputStream = getOutputStream(response, fileName);

        ExcelWriter excelWriter = EasyExcelFactory.write(outputStream, clazz)
                .registerWriteHandler(buildCellStyle())
                //.registerWriteHandler(new MonthSheetWriteHandler(fileName))
                .build();

        List<List<T>> partition = ListUtils.partition(list, size == 0 ? PER_SHEET_ROW_COUNT : size);

        int sheetNos = partition.size();

        if (sheetNos == 0) {
            sheetNos = 1;
        }

        for (int i = 0; i < sheetNos; i++) {
            WriteSheet writeSheet = EasyExcelFactory.writerSheet(i, sheetName + i).build();
            excelWriter.write(partition.get(i), writeSheet);
        }
        excelWriter.finish();
        logger.info("导出Excel完成; 所耗时间: {}", stopwatch.stop());
    }

    /**
     * 导出多个sheet页 不同对象
     *
     * @param response
     * @param list
     * @param fileName
     * @param sheetName
     * @param clazz
     * @param <T>
     */
    public static <T> void writeDifSheetExcel(HttpServletResponse response, List<T> list, String fileName, String sheetName, Class<T> clazz) {

        OutputStream outputStream = getOutputStream(response, fileName);

        ExcelWriter excelWriter = EasyExcelFactory.write(outputStream, clazz).build();

        for (int i = 0; i < 5; i++) {
            WriteSheet writeSheet = EasyExcelFactory.writerSheet(i, sheetName + i)
                    .head(clazz) // 根据导出每个sheet页的对象
                    .build();
            excelWriter.write(list, writeSheet);
        }

        excelWriter.finish();
    }


    /**
     * 导出Excel(带样式)
     */
    public static <T> void writeStyleExcel(HttpServletResponse response, List<T> list, String fileName, Class<T> clazz) {
        logger.info("<<--导出Excel开始-->>");
        Stopwatch stopwatch = Stopwatch.createStarted();
        OutputStream outputStream = getOutputStream(response, fileName);
        EasyExcelFactory.write(outputStream, clazz)
                //.head(headList)
                .registerWriteHandler(buildCellStyle())
                //.registerWriteHandler(new MonthSheetWriteHandler(fileName))
                .sheet(fileName)
                .useDefaultStyle(true)
                //.relativeHeadRowIndex(3) //设置默认样式及写入头信息开始的行数
                .relativeHeadRowIndex(0) //设置默认样式及写入头信息开始的行数
                .doWrite(list);
        logger.info("导出Excel完成; 所耗时间: {}", stopwatch.stop());
    }

    /**
     * 导出Csv格式(使用对象)
     */
    public static <T> void writeCsv(HttpServletResponse response, List<T> list, String fileName, Class<T> clazz) {
        logger.info("<<--导出Csv开始-->>");
        Stopwatch stopwatch = Stopwatch.createStarted();
        OutputStream outputStream = getOutputStream(response, fileName);
        EasyExcelFactory.write(outputStream, clazz)
                .excelType(ExcelTypeEnum.CSV)
                .charset(Charset.forName(BaseConstants.GBK))
                .sheet()
                .doWrite(list);
        logger.info("导出CSV完成; 所耗时间: {}", stopwatch.stop());
    }

    /**
     * 导出Csv格式(不使用对象,传入head)
     */
    public static <T> void writeCsvByHead(HttpServletResponse response, List<T> list, String fileName, List<List<String>> head) {
        Stopwatch stopwatch = Stopwatch.createStarted();
        OutputStream outputStream = getOutputStream(response, fileName);
        EasyExcelFactory.write(outputStream)
                .excelType(ExcelTypeEnum.CSV)
                .head(head)
                .charset(Charset.forName(BaseConstants.GBK))
                .sheet()
                .doWrite(list);
        logger.info("导出CSV完成; 所耗时间: {}", stopwatch.stop());
    }

    /**
     * 导出Excel(动态表头)
     * write时不传入class,table时传入并设置needHead为false
     */
    public static <T> void writeDynamicHeadExcel(HttpServletResponse response, List<T> list, String fileName, String sheetName, Class<T> clazz, List<List<String>> headList) {

        OutputStream outputStream = getOutputStream(response, fileName);

        EasyExcelFactory.write(outputStream)
                .head(headList)
                .sheet(sheetName)
                .table()
                .head(clazz)
                .needHead(Boolean.FALSE)
                .doWrite(list);
    }

    /**
     * 导出时生成OutputStream
     */
    public static OutputStream getOutputStream(HttpServletResponse response, String fileName) {
        // 添加响应头信息
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");//设置类型
        response.setHeader("Pragma", "No-cache");//设置头
        response.setHeader("Cache-Control", "no-cache");//设置头
        response.setDateHeader("Expires", 0);//设置日期头
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());

        // 创建本地文件
        String filePath = fileName + FileTypeConstant.XLSX_POINT;
        try {
            fileName = URLEncoder.encode(filePath, StandardCharsets.UTF_8.name());
            response.setHeader("Content-Disposition", "filename=" + fileName);
            return response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 返回ExcelReader
     *
     * @param excel         文件
     * @param clazz         实体类
     * @param excelListener
     */
    //private static <T> ExcelReader getReader(MultipartFile excel, Class<T> clazz, ExcelListener excelListener) {
    //    String filename = excel.getOriginalFilename();
    //
    //    try {
    //        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
    //            return null;
    //        }
    //
    //        InputStream inputStream = new BufferedInputStream(excel.getInputStream());
    //
    //        ExcelReader excelReader = EasyExcelFactory.read(inputStream, clazz, excelListener).build();
    //
    //        inputStream.close();
    //
    //        return excelReader;
    //    } catch (Exception e) {
    //        e.printStackTrace();
    //    }
    //    return null;
    //}

    /**
     * 构建单元格格式
     */
    private static HorizontalCellStyleStrategy buildCellStyle() {
        //表头策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //背景浅灰
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteCellStyle.setWriteFont(headWriteFont);

        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 否则无法显示背景颜色；头默认了FillPatternType
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        //背景浅绿
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        //字体大小
        contentWriteFont.setFontHeightInPoints((short) 9);
        contentWriteFont.setFontName("等线");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //contentWriteCellStyle.setWrapped(true);//设置 自动换行
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);// 下边框
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); // 左边框
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直对齐方式
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//水平居中
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为白色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);

        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("等线");
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        //自动换行
        contentWriteCellStyle.setWrapped(true);
        //文字
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteFont.setFontName("等线");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
}
